package com.ruoyi.web.controller.report;

import com.alibaba.fastjson.JSONArray;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.utils.DBUtil;
import com.ruoyi.report.domain.ReportDataSource;
import com.ruoyi.report.service.IReportDataSourceService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @version v1.0
 * @ProjectName: report
 * @ClassName: SqlTestController
 * @Description: TODO(一句话描述该类的功能)
 * @Author: sunyan
 * @Date: 2022/3/30 13:57
 */
@RestController
@RequestMapping("/report/transit")
public class ParseSqlController {

    @Autowired
    IReportDataSourceService reportDataSourceService;

    @GetMapping("sql")
    public AjaxResult doParse(@RequestParam("dataSourceId")Long dataSourceId,
                              @RequestParam("sql")String sql) {
        System.out.println(sql);
        ReportDataSource reportDataSource = reportDataSourceService.selectReportDataSourceById(dataSourceId);
        String drive = null;
        if ("mysql".equals(reportDataSource.getDatabaseType())) {
            drive = "com.mysql.cj.jdbc.Driver";
        }
        String url = "jdbc:mysql://" +reportDataSource.getIpAddress()+":" + reportDataSource.getPort() + "/" +
                reportDataSource.getBaseName() + "?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8";
        JSONArray arr = null;
        try {
            arr = DBUtil.execute(drive, url, reportDataSource.getUsername(), reportDataSource.getPassword(), sql);
        } catch (SQLException e) {
            e.printStackTrace();
            return AjaxResult.error("操作错误");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return AjaxResult.error("操作错误");
        }
        return AjaxResult.success(arr);
    }


    @GetMapping("getTables")
    public AjaxResult doGetTables(@RequestParam("dataSourceId")Long dataSourceId) {
        ReportDataSource reportDataSource = reportDataSourceService.selectReportDataSourceById(dataSourceId);
        String drive = null;
        if ("mysql".equals(reportDataSource.getDatabaseType())) {
            drive = "com.mysql.cj.jdbc.Driver";
        }
        String sql = "select table_name,table_comment from information_schema.tables WHERE table_schema='" + reportDataSource.getBaseName() + "'";
        String url = "jdbc:mysql://" +reportDataSource.getIpAddress()+":" + reportDataSource.getPort() + "/" +
                reportDataSource.getBaseName() + "?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8";
        JSONArray arr = null;
        try {
            arr = DBUtil.execute(drive, url, reportDataSource.getUsername(), reportDataSource.getPassword(), sql);
        } catch (SQLException e) {
            e.printStackTrace();
            return AjaxResult.error("操作错误");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return AjaxResult.error("操作错误");
        }
        return AjaxResult.success(arr);
    }
    @GetMapping("getFields")
    public AjaxResult doGetFields(@RequestParam("dataSourceId")Long dataSourceId) {
        ReportDataSource reportDataSource = reportDataSourceService.selectReportDataSourceById(dataSourceId);
        String drive = null;
        if ("mysql".equals(reportDataSource.getDatabaseType())) {
            drive = "com.mysql.cj.jdbc.Driver";
        }
        String tableName = "";
        String sql = "SELECT column_name as columnName, data_type as dataType, column_comment as comment from INFORMATION_SCHEMA.COLUMNS WHERE table_name = '" + tableName + "' AND table_schema = '" + reportDataSource.getBaseName() + "'";

        String url = "jdbc:mysql://" +reportDataSource.getIpAddress()+":" + reportDataSource.getPort() + "/" +
                reportDataSource.getBaseName() + "?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8";
        JSONArray arr = null;
        try {
            arr = DBUtil.execute(drive, url, reportDataSource.getUsername(), reportDataSource.getPassword(), sql);
        } catch (SQLException e) {
            e.printStackTrace();
            return AjaxResult.error("操作错误");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return AjaxResult.error("操作错误");
        }
        return AjaxResult.success(arr);
    }
}